insert overwrite table jms_dm.dm_cainiao_route_data_dt partition(dt)
select
in_from_provider_code
,in_from_provider_desc
,in_from_city_code
,in_from_city_desc
,in_from_area_code
,in_from_area_desc
,in_from_code
,in_from_name
,branch_in_center_span_days
,center_branch_out_span_days
,reserve_1
,latest_warehousing_time_quantum
,in_edge2_planned_departure
,in_to_code
,in_edge_planned_departure
,in_line_day
,in_ship_span_days
,extra_in_planned_arrival
,in_edge2_planned_arrival
,in_edge_planned_arrival
,e1_end_code
,branch_in_center_stop_time
,center_branch_out_stop_time
,e1_planned_departure
,e1_runtime
,e2_planned_departure
,e3_planned_departure
,e4_planned_departure
,e5_planned_departure
,e6_planned_departure
,e1_planned_arrival
,e2_planned_arrival
,e3_planned_arrival
,e4_planned_arrival
,e5_planned_arrival
,e6_planned_arrival
,e2_runtime
,e3_runtime
,e4_runtime
,e5_runtime
,e6_runtime
,e1_e2_stop_time
,e2_e3_stop_time
,e3_e4_stop_time
,e4_e5_stop_time
,e5_e6_stop_time
,e2_end_code
,e3_end_code
,e4_end_code
,e5_end_code
,e6_end_code
,extra_out_planned_departure
,out_edge2_planned_departure
,out_edge_planned_departure
,out_to_provider_code
,out_to_provider_desc
,out_to_city_code
,out_to_city_desc
,out_to_area_code
,out_to_area_desc
,out_to_code
,out_to_name
,out_line_day
,branch_out_span_days
,out_edge2_planned_arrival_time
,out_lastest_time
,is_main_route
,search_type
,out_collect_line_name
,e1_latest_warehousing
,e2_latest_warehousing
,e3_latest_warehousing
,e4_latest_arrival_time
,e5_latest_arrival_time
,e6_latest_arrival_time
,out_edge_latest_warehouse_time
,extra_out_latest_warehousing
,out_edge2_latest_warehouse_time
,in_edge2_latest_warehousing
,extra_in_latest_warehousing
,extra_in_planned_departure
,extra_out_collect_code
,extra_out_planned_arrival
,out_edge_planned_arrival_time
,e1_span_days
,e2_span_days
,e3_span_days
,e4_span_days
,e5_span_days
,e6_span_days
,e1_line_code
,e2_line_code
,e3_line_code
,e4_line_code
,e5_line_code
,e6_line_code
,direct_line_code
,route_contain_main_line
,rn
,'T' as is_true
,in_last_shift_code
,out_last_shift_code
,'{{ execution_date | cst_ds }}'
from
(
select *,
       ROW_NUMBER() over (partition by in_from_code,in_to_code,e1_end_code,e2_end_code,e3_end_code,e4_end_code,e5_end_code,e6_end_code,out_to_code,search_type,out_last_shift_code,latest_warehousing_time_quantum order by
concat(nvl(center_branch_out_span_days,'0'),
nvl(out_edge_planned_departure,'00:00'),
case when extra_out_collect_code is null then '0' else if(out_edge_planned_arrival_time > extra_out_latest_warehousing,'1','0') end,
case when extra_out_collect_code is null then '0' else if(extra_out_latest_warehousing > extra_out_planned_departure,'1','0') end,
nvl(extra_out_planned_departure,'00:00'),
case when extra_out_collect_code is null then if(out_edge_planned_arrival_time > out_edge2_latest_warehouse_time,'1','0') else if(extra_out_planned_arrival > out_edge2_latest_warehouse_time,'1','0') end,
if(out_edge2_latest_warehouse_time > out_edge2_planned_departure,'1','0'),
nvl(out_edge2_planned_departure,'00:00')) desc,
concat(
nvl(reserve_1,0)+nvl(in_line_day,0),
nvl(in_edge_planned_departure,'00:00'),
case when in_edge_planned_arrival     > in_edge2_latest_warehousing then '1' else '0' end,
case when in_edge2_latest_warehousing > in_edge2_planned_departure  then '1' else '0' end,
nvl(in_edge2_planned_departure,'00:00'),
case when in_edge2_planned_arrival    > extra_in_latest_warehousing then '1' else '0' end,
case when extra_in_latest_warehousing > extra_in_planned_departure  then '1' else '0' end,
nvl(extra_in_planned_departure,'00:00'),
nvl(branch_in_center_span_days,'0'),
nvl(e1_planned_departure,'00:00'),nvl(e1_span_days,'0'),
case when e1_e2_stop_time is null then '0' when cast(e1_e2_stop_time as int) is null then '0' else format_string('%04d', cast(e1_e2_stop_time as int)) end,
nvl(e2_planned_departure,'00:00'),nvl(e2_span_days,'0'),
case when e2_e3_stop_time is null then '0' when cast(e2_e3_stop_time as int) is null then '0' else format_string('%04d', cast(e2_e3_stop_time as int)) end,
nvl(e3_planned_departure,'00:00'),nvl(e3_span_days,'0'),
case when e3_e4_stop_time is null then '0' when cast(e3_e4_stop_time as int) is null then '0' else format_string('%04d', cast(e3_e4_stop_time as int)) end,
nvl(e4_planned_departure,'00:00'),nvl(e4_span_days,'0'),
case when e4_e5_stop_time is null then '0' when cast(e4_e5_stop_time as int) is null then '0' else format_string('%04d', cast(e4_e5_stop_time as int)) end,
nvl(e5_planned_departure,'00:00'),nvl(e5_span_days,'0'),
case when e5_e6_stop_time is null then '0' when cast(e5_e6_stop_time as int) is null then '0' else format_string('%04d', cast(e5_e6_stop_time as int)) end,
nvl(e6_planned_departure,'00:00'),nvl(e6_span_days,'0')) desc) as rn
from (
         select in_from_provider_code                                                                 as in_from_provider_code
              ,in_from_provider_desc                                                                  as in_from_provider_desc
              ,in_from_city_code                                                                      as in_from_city_code
              ,in_from_city_desc                                                                      as in_from_city_desc
              ,in_from_area_code                                                                      as in_from_area_code
              ,in_from_area_desc                                                                      as in_from_area_desc
              ,in_from_code
              ,in_from_name                                                                           as in_from_name
              ,if(length(branch_in_center_span_days) = 0, null,branch_in_center_span_days)            as branch_in_center_span_days
              ,if(length(center_branch_out_span_days) = 0, null,center_branch_out_span_days)          as center_branch_out_span_days
              ,nvl(reserve_1, 0)                                                                      as reserve_1
              ,latest_warehousing_time_quantum                                                        as latest_warehousing_time_quantum
              ,if(length(in_edge2_planned_departure) = 0, null,in_edge2_planned_departure)            as in_edge2_planned_departure
              ,if(length(in_to_code) = 0, null, in_to_code)                                           as in_to_code
              ,if(length(in_edge_planned_departure) = 0, null,in_edge_planned_departure)              as in_edge_planned_departure
              ,nvl(in_line_day, 0)                                                                    as in_line_day
              ,if(length(in_ship_span_days) = 0, null, in_ship_span_days)                             as in_ship_span_days
              ,if(length(extra_in_planned_arrival) = 0, null,extra_in_planned_arrival)                as extra_in_planned_arrival
              ,if(length(in_edge2_planned_arrival) = 0, null,in_edge2_planned_arrival)                as in_edge2_planned_arrival
              ,if(length(in_edge_planned_arrival) = 0, null,in_edge_planned_arrival)                  as in_edge_planned_arrival
              ,if(length(e1_end_code) = 0, null, e1_end_code)                                         as e1_end_code
              ,if(length(branch_in_center_stop_time) = 0, null,branch_in_center_stop_time)            as branch_in_center_stop_time
              ,if(length(center_branch_out_stop_time) = 0, null,center_branch_out_stop_time)          as center_branch_out_stop_time
              ,if(length(e1_planned_departure) = 0, null, e1_planned_departure)                       as e1_planned_departure
              ,if(length(e1_runtime) = 0, null, e1_runtime)                                           as e1_runtime
              ,if(length(e2_planned_departure) = 0, null, e2_planned_departure)                       as e2_planned_departure
              ,if(length(e3_planned_departure) = 0, null, e3_planned_departure)                       as e3_planned_departure
              ,if(length(e4_planned_departure) = 0, null, e4_planned_departure)                       as e4_planned_departure
              ,if(length(e5_planned_departure) = 0, null, e5_planned_departure)                       as e5_planned_departure
              ,if(length(e6_planned_departure) = 0, null, e6_planned_departure)                       as e6_planned_departure
              ,if(length(e1_planned_arrival) = 0, null, e1_planned_arrival)                           as e1_planned_arrival
              ,if(length(e2_planned_arrival) = 0, null, e2_planned_arrival)                           as e2_planned_arrival
              ,if(length(e3_planned_arrival) = 0, null, e3_planned_arrival)                           as e3_planned_arrival
              ,if(length(e4_planned_arrival) = 0, null, e4_planned_arrival)                           as e4_planned_arrival
              ,if(length(e5_planned_arrival) = 0, null, e5_planned_arrival)                           as e5_planned_arrival
              ,if(length(e6_planned_arrival) = 0, null, e6_planned_arrival)                           as e6_planned_arrival
              ,if(length(e2_runtime) = 0, null, e2_runtime)                                           as e2_runtime
              ,if(length(e3_runtime) = 0, null, e3_runtime)                                           as e3_runtime
              ,if(length(e4_runtime) = 0, null, e4_runtime)                                           as e4_runtime
              ,if(length(e5_runtime) = 0, null, e5_runtime)                                           as e5_runtime
              ,if(length(e6_runtime) = 0, null, e6_runtime)                                           as e6_runtime
              ,if(length(e1_e2_stop_time) = 0, null, e1_e2_stop_time)                                 as e1_e2_stop_time
              ,if(length(e2_e3_stop_time) = 0, null, e2_e3_stop_time)                                 as e2_e3_stop_time
              ,if(length(e3_e4_stop_time) = 0, null, e3_e4_stop_time)                                 as e3_e4_stop_time
              ,if(length(e4_e5_stop_time) = 0, null, e4_e5_stop_time)                                 as e4_e5_stop_time
              ,if(length(e5_e6_stop_time) = 0, null, e5_e6_stop_time)                                 as e5_e6_stop_time
              ,if(length(e2_end_code) = 0, null, e2_end_code)                                         as e2_end_code
              ,if(length(e3_end_code) = 0, null, e3_end_code)                                         as e3_end_code
              ,if(length(e4_end_code) = 0, null, e4_end_code)                                         as e4_end_code
              ,if(length(e5_end_code) = 0, null, e5_end_code)                                         as e5_end_code
              ,if(length(e6_end_code) = 0, null, e6_end_code)                                         as e6_end_code
              ,if(length(extra_out_planned_departure) = 0, null,extra_out_planned_departure)          as extra_out_planned_departure
              ,if(length(out_edge2_planned_departure) = 0, null,out_edge2_planned_departure)          as out_edge2_planned_departure
              ,if(length(out_edge_planned_departure) = 0, null,out_edge_planned_departure)            as out_edge_planned_departure
              ,out_to_provider_code                                                                   as out_to_provider_code
              ,out_to_provider_desc                                                                   as out_to_provider_desc
              ,out_to_city_code                                                                       as out_to_city_code
              ,out_to_city_desc                                                                       as out_to_city_desc
              ,out_to_area_code                                                                       as out_to_area_code
              ,out_to_area_desc                                                                       as out_to_area_desc
              ,out_to_code                                                                            as out_to_code
              ,out_to_name                                                                            as out_to_name
              ,if(length(out_line_day) = 0, 0, out_line_day)                                          as out_line_day
              ,branch_out_span_days                                                                   as branch_out_span_days
              ,out_edge2_planned_arrival_time                                                         as out_edge2_planned_arrival_time
              ,'23:59:59'                                                                             as out_lastest_time
              ,is_main_route                                                                          as is_main_route
              ,search_type                                                                            as search_type
              ,out_collect_line_name                                                                  as out_collect_line_name
              ,if(length(e1_latest_warehousing) = 0, null, e1_latest_warehousing)                     as e1_latest_warehousing
              ,if(length(e2_latest_warehousing) = 0, null, e2_latest_warehousing)                     as e2_latest_warehousing
              ,if(length(e3_latest_warehousing) = 0, null, e3_latest_warehousing)                     as e3_latest_warehousing
              ,if(length(e4_latest_arrival_time) = 0, null, e4_latest_arrival_time)                   as e4_latest_arrival_time
              ,if(length(e5_latest_arrival_time) = 0, null, e5_latest_arrival_time)                   as e5_latest_arrival_time
              ,if(length(e6_latest_arrival_time) = 0, null, e6_latest_arrival_time)                   as e6_latest_arrival_time
              ,if(length(out_edge_latest_warehouse_time) = 0, null,out_edge_latest_warehouse_time)    as out_edge_latest_warehouse_time
              ,if(length(extra_out_latest_warehousing) = 0, null,extra_out_latest_warehousing)        as extra_out_latest_warehousing
              ,if(length(out_edge2_latest_warehouse_time) = 0, null,out_edge2_latest_warehouse_time)  as out_edge2_latest_warehouse_time
              ,if(length(in_edge2_latest_warehousing) = 0, null,in_edge2_latest_warehousing)          as in_edge2_latest_warehousing
              ,if(length(extra_in_latest_warehousing) = 0, null,extra_in_latest_warehousing)          as extra_in_latest_warehousing
              ,if(length(extra_in_planned_departure) = 0, null,extra_in_planned_departure)            as extra_in_planned_departure
              ,if(length(extra_out_collect_code) = 0, null,extra_out_collect_code)                    as extra_out_collect_code
              ,if(length(extra_out_planned_arrival) = 0, null,extra_out_planned_arrival)              as extra_out_planned_arrival
              ,if(length(out_edge_planned_arrival_time) = 0, null,out_edge_planned_arrival_time)      as out_edge_planned_arrival_time
              ,nvl(e1_span_days,0)                                                                    as e1_span_days
              ,nvl(e2_span_days,0)                                                                    as e2_span_days
              ,nvl(e3_span_days,0)                                                                    as e3_span_days
              ,nvl(e4_span_days,0)                                                                    as e4_span_days
              ,nvl(e5_span_days,0)                                                                    as e5_span_days
              ,nvl(e6_span_days,0)                                                                    as e6_span_days
              ,if(length (e1_line_code)=0,null,replace(e1_line_code,'(计重)','')) as e1_line_code
              ,if(length (e2_line_code)=0,null,replace(e2_line_code,'(计重)','')) as e2_line_code
              ,if(length (e3_line_code)=0,null,replace(e3_line_code,'(计重)','')) as e3_line_code
              ,if(length (e4_line_code)=0,null,replace(e4_line_code,'(计重)','')) as e4_line_code
              ,if(length (e5_line_code)=0,null,replace(e5_line_code,'(计重)','')) as e5_line_code
              ,if(length (e6_line_code)=0,null,replace(e6_line_code,'(计重)','')) as e6_line_code
              ,null as direct_line_code
              ,route_contain_main_line
              ,in_last_shift_code
              ,out_last_shift_code
         from jms_dm.dm_route_whole_base_dt
         where dt = '{{ execution_date | cst_ds }}'
           and is_main_route <> 2
           and in_from_code <> out_to_code
           and (out_collect_line_name!='成都-拉萨集散点1201(2班)' or out_collect_line_name is null)
           and latest_warehousing_time_quantum is not null
           and in_from_name is not null
           and out_to_code is not null
           and nvl(route_contain_main_line, 0) <> 1 --剔除直发
         union ALL
         select in_from_provider_code                                                                  as in_from_provider_code
              ,in_from_provider_desc                                                                  as in_from_provider_desc
              ,in_from_city_code                                                                      as in_from_city_code
              ,in_from_city_desc                                                                      as in_from_city_desc
              ,in_from_area_code                                                                      as in_from_area_code
              ,in_from_area_desc                                                                      as in_from_area_desc
              ,in_from_code
              ,in_from_name                                                                           as in_from_name
              ,if(length(branch_in_center_span_days) = 0, null,branch_in_center_span_days)            as branch_in_center_span_days
              ,if(length(center_branch_out_span_days) = 0, null,center_branch_out_span_days)          as center_branch_out_span_days
              ,nvl(reserve_1, 0)                                                                      as reserve_1
              ,latest_warehousing_time_quantum                                                        as latest_warehousing_time_quantum
              ,if(length(in_edge2_planned_departure) = 0, null,in_edge2_planned_departure)            as in_edge2_planned_departure
              ,if(length(in_to_code) = 0, null, in_to_code)                                           as in_to_code
              ,if(length(in_edge_planned_departure) = 0, null,in_edge_planned_departure)              as in_edge_planned_departure
              ,nvl(in_line_day, 0)                                                                    as in_line_day
              ,if(length(in_ship_span_days) = 0, null, in_ship_span_days)                             as in_ship_span_days
              ,if(length(extra_in_planned_arrival) = 0, null,extra_in_planned_arrival)                as extra_in_planned_arrival
              ,if(length(in_edge2_planned_arrival) = 0, null,in_edge2_planned_arrival)                as in_edge2_planned_arrival
              ,if(length(in_edge_planned_arrival) = 0, null,in_edge_planned_arrival)                  as in_edge_planned_arrival
              ,if(length(e1_end_code) = 0, null, e1_end_code)                                         as e1_end_code
              ,if(length(branch_in_center_stop_time) = 0, null,branch_in_center_stop_time)            as branch_in_center_stop_time
              ,if(length(center_branch_out_stop_time) = 0, null,center_branch_out_stop_time)          as center_branch_out_stop_time
              ,if(length(e1_planned_departure) = 0, null, e1_planned_departure)                       as e1_planned_departure
              ,if(length(e1_runtime) = 0, null, e1_runtime)                                           as e1_runtime
              ,if(length(e2_planned_departure) = 0, null, e2_planned_departure)                       as e2_planned_departure
              ,if(length(e3_planned_departure) = 0, null, e3_planned_departure)                       as e3_planned_departure
              ,if(length(e4_planned_departure) = 0, null, e4_planned_departure)                       as e4_planned_departure
              ,if(length(e5_planned_departure) = 0, null, e5_planned_departure)                       as e5_planned_departure
              ,if(length(e6_planned_departure) = 0, null, e6_planned_departure)                       as e6_planned_departure
              ,if(length(e1_planned_arrival) = 0, null, e1_planned_arrival)                           as e1_planned_arrival
              ,if(length(e2_planned_arrival) = 0, null, e2_planned_arrival)                           as e2_planned_arrival
              ,if(length(e3_planned_arrival) = 0, null, e3_planned_arrival)                           as e3_planned_arrival
              ,if(length(e4_planned_arrival) = 0, null, e4_planned_arrival)                           as e4_planned_arrival
              ,if(length(e5_planned_arrival) = 0, null, e5_planned_arrival)                           as e5_planned_arrival
              ,if(length(e6_planned_arrival) = 0, null, e6_planned_arrival)                           as e6_planned_arrival
              ,if(length(e2_runtime) = 0, null, e2_runtime)                                           as e2_runtime
              ,if(length(e3_runtime) = 0, null, e3_runtime)                                           as e3_runtime
              ,if(length(e4_runtime) = 0, null, e4_runtime)                                           as e4_runtime
              ,if(length(e5_runtime) = 0, null, e5_runtime)                                           as e5_runtime
              ,if(length(e6_runtime) = 0, null, e6_runtime)                                           as e6_runtime
              ,if(length(e1_e2_stop_time) = 0, null, e1_e2_stop_time)                                 as e1_e2_stop_time
              ,if(length(e2_e3_stop_time) = 0, null, e2_e3_stop_time)                                 as e2_e3_stop_time
              ,if(length(e3_e4_stop_time) = 0, null, e3_e4_stop_time)                                 as e3_e4_stop_time
              ,if(length(e4_e5_stop_time) = 0, null, e4_e5_stop_time)                                 as e4_e5_stop_time
              ,if(length(e5_e6_stop_time) = 0, null, e5_e6_stop_time)                                 as e5_e6_stop_time
              ,if(length(e2_end_code) = 0, null, e2_end_code)                                         as e2_end_code
              ,if(length(e3_end_code) = 0, null, e3_end_code)                                         as e3_end_code
              ,if(length(e4_end_code) = 0, null, e4_end_code)                                         as e4_end_code
              ,if(length(e5_end_code) = 0, null, e5_end_code)                                         as e5_end_code
              ,if(length(e6_end_code) = 0, null, e6_end_code)                                         as e6_end_code
              ,if(length(extra_out_planned_departure) = 0, null,extra_out_planned_departure)          as extra_out_planned_departure
              ,if(length(out_edge2_planned_departure) = 0, null,out_edge2_planned_departure)          as out_edge2_planned_departure
              ,if(length(out_edge_planned_departure) = 0, null,out_edge_planned_departure)            as out_edge_planned_departure
              ,out_to_provider_code                                                                   as out_to_provider_code
              ,out_to_provider_desc                                                                   as out_to_provider_desc
              ,out_to_city_code                                                                       as out_to_city_code
              ,out_to_city_desc                                                                       as out_to_city_desc
              ,out_to_area_code                                                                       as out_to_area_code
              ,out_to_area_desc                                                                       as out_to_area_desc
              ,out_to_code                                                                            as out_to_code
              ,out_to_name                                                                            as out_to_name
              ,if(length(out_line_day) = 0, 0, out_line_day)                                          as out_line_day
              ,branch_out_span_days                                                                   as branch_out_span_days
              ,out_edge2_planned_arrival_time                                                         as out_edge2_planned_arrival_time
              ,'23:59:59'                                                                             as out_lastest_time
              ,is_main_route                                                                          as is_main_route
              ,search_type                                                                            as search_type
              ,out_collect_line_name                                                                  as out_collect_line_name
              ,if(length(e1_latest_warehousing) = 0, null, e1_latest_warehousing)                     as e1_latest_warehousing
              ,if(length(e2_latest_warehousing) = 0, null, e2_latest_warehousing)                     as e2_latest_warehousing
              ,if(length(e3_latest_warehousing) = 0, null, e3_latest_warehousing)                     as e3_latest_warehousing
              ,if(length(e4_latest_arrival_time) = 0, null, e4_latest_arrival_time)                   as e4_latest_arrival_time
              ,if(length(e5_latest_arrival_time) = 0, null, e5_latest_arrival_time)                   as e5_latest_arrival_time
              ,if(length(e6_latest_arrival_time) = 0, null, e6_latest_arrival_time)                   as e6_latest_arrival_time
              ,if(length(out_edge_latest_warehouse_time) = 0, null,out_edge_latest_warehouse_time)    as out_edge_latest_warehouse_time
              ,if(length(extra_out_latest_warehousing) = 0, null,extra_out_latest_warehousing)        as extra_out_latest_warehousing
              ,if(length(out_edge2_latest_warehouse_time) = 0, null,out_edge2_latest_warehouse_time)  as out_edge2_latest_warehouse_time
              ,if(length(in_edge2_latest_warehousing) = 0, null,in_edge2_latest_warehousing)          as in_edge2_latest_warehousing
              ,if(length(extra_in_latest_warehousing) = 0, null,extra_in_latest_warehousing)          as extra_in_latest_warehousing
              ,if(length(extra_in_planned_departure) = 0, null,extra_in_planned_departure)            as extra_in_planned_departure
              ,if(length(extra_out_collect_code) = 0, null,extra_out_collect_code)                    as extra_out_collect_code
              ,if(length(extra_out_planned_arrival) = 0, null,extra_out_planned_arrival)              as extra_out_planned_arrival
              ,if(length(out_edge_planned_arrival_time) = 0, null,out_edge_planned_arrival_time)      as out_edge_planned_arrival_time
              ,nvl(e1_span_days,0)                                                                    as e1_span_days
              ,nvl(e2_span_days,0)                                                                    as e2_span_days
              ,nvl(e3_span_days,0)                                                                    as e3_span_days
              ,nvl(e4_span_days,0)                                                                    as e4_span_days
              ,nvl(e5_span_days,0)                                                                    as e5_span_days
              ,nvl(e6_span_days,0)                                                                    as e6_span_days
              ,if(length (e1_line_code)=0,null,replace(e1_line_code,'(计重)','')) as e1_line_code
              ,if(length (e2_line_code)=0,null,replace(e2_line_code,'(计重)','')) as e2_line_code
              ,if(length (e3_line_code)=0,null,replace(e3_line_code,'(计重)','')) as e3_line_code
              ,if(length (e4_line_code)=0,null,replace(e4_line_code,'(计重)','')) as e4_line_code
              ,if(length (e5_line_code)=0,null,replace(e5_line_code,'(计重)','')) as e5_line_code
              ,if(length (e6_line_code)=0,null,replace(e6_line_code,'(计重)','')) as e6_line_code
              ,case when direct_route_type=1 then if(length (in_network_line_name)=0,null,in_network_line_name)
                    when direct_route_type=2 then if(length (in_collect_line_name)=0,null,in_collect_line_name) end as direct_line_code
              ,route_contain_main_line
              ,in_last_shift_code
              ,out_last_shift_code
         from jms_dm.dm_route_whole_direct_base_dt
         where dt = '{{ execution_date | cst_ds }}'
           and is_main_route <> 2
           and in_from_code <> out_to_code
           and (out_collect_line_name!='成都-拉萨集散点1201(2班)' or out_collect_line_name is null)
           and latest_warehousing_time_quantum is not null
           and in_from_name is not null
           and out_to_code is not null
     )
)where rn =1
union all
select
in_from_provider_code
,in_from_provider_desc
,in_from_city_code
,in_from_city_desc
,in_from_area_code
,in_from_area_desc
,in_from_code
,in_from_name
,branch_in_center_span_days
,center_branch_out_span_days
,reserve_1
,latest_warehousing_time_quantum
,in_edge2_planned_departure
,in_to_code
,in_edge_planned_departure
,in_line_day
,in_ship_span_days
,extra_in_planned_arrival
,in_edge2_planned_arrival
,in_edge_planned_arrival
,e1_end_code
,branch_in_center_stop_time
,center_branch_out_stop_time
,e1_planned_departure
,e1_runtime
,e2_planned_departure
,e3_planned_departure
,e4_planned_departure
,e5_planned_departure
,e6_planned_departure
,e1_planned_arrival
,e2_planned_arrival
,e3_planned_arrival
,e4_planned_arrival
,e5_planned_arrival
,e6_planned_arrival
,e2_runtime
,e3_runtime
,e4_runtime
,e5_runtime
,e6_runtime
,e1_e2_stop_time
,e2_e3_stop_time
,e3_e4_stop_time
,e4_e5_stop_time
,e5_e6_stop_time
,e2_end_code
,e3_end_code
,e4_end_code
,e5_end_code
,e6_end_code
,extra_out_planned_departure
,out_edge2_planned_departure
,out_edge_planned_departure
,out_to_provider_code
,out_to_provider_desc
,out_to_city_code
,out_to_city_desc
,out_to_area_code
,out_to_area_desc
,out_to_code
,out_to_name
,out_line_day
,branch_out_span_days
,out_edge2_planned_arrival_time
,out_lastest_time
,is_main_route
,search_type
,out_collect_line_name
,e1_latest_warehousing
,e2_latest_warehousing
,e3_latest_warehousing
,e4_latest_arrival_time
,e5_latest_arrival_time
,e6_latest_arrival_time
,out_edge_latest_warehouse_time
,extra_out_latest_warehousing
,out_edge2_latest_warehouse_time
,in_edge2_latest_warehousing
,extra_in_latest_warehousing
,extra_in_planned_departure
,extra_out_collect_code
,extra_out_planned_arrival
,out_edge_planned_arrival_time
,e1_span_days
,e2_span_days
,e3_span_days
,e4_span_days
,e5_span_days
,e6_span_days
,e1_line_code
,e2_line_code
,e3_line_code
,e4_line_code
,e5_line_code
,e6_line_code
,direct_line_code
,route_contain_main_line
,rn
,'T' as is_true
,in_last_shift_code
,out_last_shift_code
,'{{ execution_date | cst_ds }}'
from
    (
        select in_from_provider_code                                                                  as in_from_provider_code
             ,in_from_provider_desc                                                                  as in_from_provider_desc
             ,in_from_city_code                                                                      as in_from_city_code
             ,in_from_city_desc                                                                      as in_from_city_desc
             ,in_from_area_code                                                                      as in_from_area_code
             ,in_from_area_desc                                                                      as in_from_area_desc
             ,in_from_code
             ,in_from_name                                                                           as in_from_name
             ,if(length(branch_in_center_span_days) = 0, null,branch_in_center_span_days)            as branch_in_center_span_days
             ,if(length(center_branch_out_span_days) = 0, null,center_branch_out_span_days)          as center_branch_out_span_days
             ,nvl(reserve_1, 0)                                                                      as reserve_1
             ,latest_warehousing_time_quantum                                                        as latest_warehousing_time_quantum
             ,if(length(in_edge2_planned_departure) = 0, null,in_edge2_planned_departure)            as in_edge2_planned_departure
             ,if(length(in_to_code) = 0, null, in_to_code)                                           as in_to_code
             ,if(length(in_edge_planned_departure) = 0, null,in_edge_planned_departure)              as in_edge_planned_departure
             ,nvl(in_line_day, 0)                                                                    as in_line_day
             ,if(length(in_ship_span_days) = 0, null, in_ship_span_days)                             as in_ship_span_days
             ,if(length(extra_in_planned_arrival) = 0, null,extra_in_planned_arrival)                as extra_in_planned_arrival
             ,if(length(in_edge2_planned_arrival) = 0, null,in_edge2_planned_arrival)                as in_edge2_planned_arrival
             ,if(length(in_edge_planned_arrival) = 0, null,in_edge_planned_arrival)                  as in_edge_planned_arrival
             ,if(length(e1_end_code) = 0, null, e1_end_code)                                         as e1_end_code
             ,if(length(branch_in_center_stop_time) = 0, null,branch_in_center_stop_time)            as branch_in_center_stop_time
             ,if(length(center_branch_out_stop_time) = 0, null,center_branch_out_stop_time)          as center_branch_out_stop_time
             ,if(length(e1_planned_departure) = 0, null, e1_planned_departure)                       as e1_planned_departure
             ,if(length(e1_runtime) = 0, null, e1_runtime)                                           as e1_runtime
             ,if(length(e2_planned_departure) = 0, null, e2_planned_departure)                       as e2_planned_departure
             ,if(length(e3_planned_departure) = 0, null, e3_planned_departure)                       as e3_planned_departure
             ,if(length(e4_planned_departure) = 0, null, e4_planned_departure)                       as e4_planned_departure
             ,if(length(e5_planned_departure) = 0, null, e5_planned_departure)                       as e5_planned_departure
             ,if(length(e6_planned_departure) = 0, null, e6_planned_departure)                       as e6_planned_departure
             ,if(length(e1_planned_arrival) = 0, null, e1_planned_arrival)                           as e1_planned_arrival
             ,if(length(e2_planned_arrival) = 0, null, e2_planned_arrival)                           as e2_planned_arrival
             ,if(length(e3_planned_arrival) = 0, null, e3_planned_arrival)                           as e3_planned_arrival
             ,if(length(e4_planned_arrival) = 0, null, e4_planned_arrival)                           as e4_planned_arrival
             ,if(length(e5_planned_arrival) = 0, null, e5_planned_arrival)                           as e5_planned_arrival
             ,if(length(e6_planned_arrival) = 0, null, e6_planned_arrival)                           as e6_planned_arrival
             ,if(length(e2_runtime) = 0, null, e2_runtime)                                           as e2_runtime
             ,if(length(e3_runtime) = 0, null, e3_runtime)                                           as e3_runtime
             ,if(length(e4_runtime) = 0, null, e4_runtime)                                           as e4_runtime
             ,if(length(e5_runtime) = 0, null, e5_runtime)                                           as e5_runtime
             ,if(length(e6_runtime) = 0, null, e6_runtime)                                           as e6_runtime
             ,if(length(e1_e2_stop_time) = 0, null, e1_e2_stop_time)                                 as e1_e2_stop_time
             ,if(length(e2_e3_stop_time) = 0, null, e2_e3_stop_time)                                 as e2_e3_stop_time
             ,if(length(e3_e4_stop_time) = 0, null, e3_e4_stop_time)                                 as e3_e4_stop_time
             ,if(length(e4_e5_stop_time) = 0, null, e4_e5_stop_time)                                 as e4_e5_stop_time
             ,if(length(e5_e6_stop_time) = 0, null, e5_e6_stop_time)                                 as e5_e6_stop_time
             ,if(length(e2_end_code) = 0, null, e2_end_code)                                         as e2_end_code
             ,if(length(e3_end_code) = 0, null, e3_end_code)                                         as e3_end_code
             ,if(length(e4_end_code) = 0, null, e4_end_code)                                         as e4_end_code
             ,if(length(e5_end_code) = 0, null, e5_end_code)                                         as e5_end_code
             ,if(length(e6_end_code) = 0, null, e6_end_code)                                         as e6_end_code
             ,if(length(extra_out_planned_departure) = 0, null,extra_out_planned_departure)          as extra_out_planned_departure
             ,if(length(out_edge2_planned_departure) = 0, null,out_edge2_planned_departure)          as out_edge2_planned_departure
             ,if(length(out_edge_planned_departure) = 0, null,out_edge_planned_departure)            as out_edge_planned_departure
             ,out_to_provider_code                                                                   as out_to_provider_code
             ,out_to_provider_desc                                                                   as out_to_provider_desc
             ,out_to_city_code                                                                       as out_to_city_code
             ,out_to_city_desc                                                                       as out_to_city_desc
             ,out_to_area_code                                                                       as out_to_area_code
             ,out_to_area_desc                                                                       as out_to_area_desc
             ,out_to_code                                                                            as out_to_code
             ,out_to_name                                                                            as out_to_name
             ,if(length(out_line_day) = 0, 0, out_line_day)                                          as out_line_day
             ,branch_out_span_days                                                                   as branch_out_span_days
             ,out_edge2_planned_arrival_time                                                         as out_edge2_planned_arrival_time
             ,'23:59:59'                                                                             as out_lastest_time
             ,is_main_route                                                                          as is_main_route
             ,search_type                                                                            as search_type
             ,out_collect_line_name                                                                  as out_collect_line_name
             ,if(length(e1_latest_warehousing) = 0, null, e1_latest_warehousing)                     as e1_latest_warehousing
             ,if(length(e2_latest_warehousing) = 0, null, e2_latest_warehousing)                     as e2_latest_warehousing
             ,if(length(e3_latest_warehousing) = 0, null, e3_latest_warehousing)                     as e3_latest_warehousing
             ,if(length(e4_latest_arrival_time) = 0, null, e4_latest_arrival_time)                   as e4_latest_arrival_time
             ,if(length(e5_latest_arrival_time) = 0, null, e5_latest_arrival_time)                   as e5_latest_arrival_time
             ,if(length(e6_latest_arrival_time) = 0, null, e6_latest_arrival_time)                   as e6_latest_arrival_time
             ,if(length(out_edge_latest_warehouse_time) = 0, null,out_edge_latest_warehouse_time)    as out_edge_latest_warehouse_time
             ,if(length(extra_out_latest_warehousing) = 0, null,extra_out_latest_warehousing)        as extra_out_latest_warehousing
             ,if(length(out_edge2_latest_warehouse_time) = 0, null,out_edge2_latest_warehouse_time)  as out_edge2_latest_warehouse_time
             ,if(length(in_edge2_latest_warehousing) = 0, null,in_edge2_latest_warehousing)          as in_edge2_latest_warehousing
             ,if(length(extra_in_latest_warehousing) = 0, null,extra_in_latest_warehousing)          as extra_in_latest_warehousing
             ,if(length(extra_in_planned_departure) = 0, null,extra_in_planned_departure)            as extra_in_planned_departure
             ,if(length(extra_out_collect_code) = 0, null,extra_out_collect_code)                    as extra_out_collect_code
             ,if(length(extra_out_planned_arrival) = 0, null,extra_out_planned_arrival)              as extra_out_planned_arrival
             ,if(length(out_edge_planned_arrival_time) = 0, null,out_edge_planned_arrival_time)      as out_edge_planned_arrival_time
             ,nvl(e1_span_days,0)                                                                    as e1_span_days
             ,nvl(e2_span_days,0)                                                                    as e2_span_days
             ,nvl(e3_span_days,0)                                                                    as e3_span_days
             ,nvl(e4_span_days,0)                                                                    as e4_span_days
             ,nvl(e5_span_days,0)                                                                    as e5_span_days
             ,nvl(e6_span_days,0)                                                                    as e6_span_days
             ,null as e1_line_code
             ,null as e2_line_code
             ,null as e3_line_code
             ,null as e4_line_code
             ,null as e5_line_code
             ,null as e6_line_code
             ,null as direct_line_code
             ,0    as route_contain_main_line
             ,out_last_shift_code
             ,in_last_shift_code
             ,ROW_NUMBER() over (partition by in_from_code,out_to_code,search_type,out_last_shift_code,latest_warehousing_time_quantum order by warehouse_end_time desc) as rn
        from jms_dm.dm_route_whole_base_dt
        where dt = '{{ execution_date | cst_ds }}' and in_from_code = out_to_code and LENGTH(in_to_code) =0
          and in_from_name is not null and out_to_code is not null and region = 'local'
    ) where rn =1
;
